﻿<cfsilent>
	<cfscript>

		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");

		sql = "SELECT b.sbj_id,
					   s.sbj_direction,
					   s.stu_id,
					   s.stu_name,
					   c.institute_name,
					   t_subject.sbj_name,
					   b.grade
				FROM 
					t_student s 
					  INNER JOIN t_student_status a ON a.stu_id = s.stu_id
					  INNER JOIN t_class b ON b.cls_id = s.cls_id 
					  INNER JOIN t_subject ON t_subject.sbj_id = b.sbj_id
					  INNER JOIN t_institute c ON c.institute_id = t_subject.institute_id
				WHERE 
					SUBSTR(a.student_prop,13,1) = '1'
					ORDER BY s.stu_id";
					
		queryObj = new query( datasource=application.dnsSlave);
		
		rs_history= queryObj.execute( sql=sql ).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( "本校提前毕业学生名单", "utf-8");

		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew( "本校提前毕业学生名单", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学号,姓名,学院,专业,专业方向,年级");
		
		row = 1;
		
		for (a = 1; a lte rs_history.recordCount; a++) {
			
			row++;
			subjectDirectionID = rs_history["sbj_id"][a] & rs_history["sbj_direction"][a];
			
			sql = "SELECT a.sbj_name 
					FROM t_subject a 
					WHERE a.sbj_id = :subjectDirectionID ";
					
			queryObj = new query( datasource=application.dnsSlave);
			queryObj.addParam( name="subjectDirectionID", value=subjectDirectionID , cfsqltype="cf_sql_varchar" );
			
			rs_subjectDirection = queryObj.execute( sql=sql ).getResult();
			
			if(rs_subjectDirection.recordCount EQ 0){
				subjectDirectionName="无";
			}else{
				subjectDirectionName=rs_subjectDirection.sbj_name;
			}
			/* 构建提前毕业学生信息 */
			rowData = rs_history["stu_id"][a]
						& "," & rs_history["stu_name"][a]
						& "," & rs_history["institute_name"][a]
						& "," & rs_history["sbj_name"][a]
						& "," & subjectDirectionName
						& "," & rs_history["grade"][a];
						
			/* 写入提前毕业学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
		}
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>